"""Performance record repository for database operations."""

from datetime import date
from typing import Any, Optional

from sqlalchemy import and_, extract, func
from sqlalchemy.orm import Session

from ..models.performance_record import PerformanceRecord
from ..models.user import User
from ..schemas.performance_record import PerformanceRecordCreate, PerformanceRecordUpdate
from .base import Repository


class PerformanceRecordRepository(
    Repository[PerformanceRecord, PerformanceRecordCreate, PerformanceRecordUpdate]
):
    """Repository for performance record CRUD and analytics."""

    def __init__(self, db: Session):
        super().__init__(PerformanceRecord, db)

    def get_by_user_and_date(self, user_id: int, record_date: date) -> Optional[PerformanceRecord]:
        """Get performance record for a specific user and date.

        Args:
            user_id: User ID
            record_date: Record date

        Returns:
            PerformanceRecord if found, None otherwise
        """
        return (
            self.session.query(PerformanceRecord)
            .filter(
                and_(
                    PerformanceRecord.user_id == user_id,
                    PerformanceRecord.record_date == record_date,
                )
            )
            .first()
        )

    def get_by_date_range(
        self,
        user_id: Optional[int] = None,
        start_date: Optional[date] = None,
        end_date: Optional[date] = None,
    ) -> list[PerformanceRecord]:
        """Get performance records filtered by date range and optionally user.

        Args:
            user_id: Optional user ID filter
            start_date: Optional start date filter
            end_date: Optional end date filter

        Returns:
            List of performance records
        """
        query = self.session.query(PerformanceRecord)

        if user_id:
            query = query.filter(PerformanceRecord.user_id == user_id)
        if start_date:
            query = query.filter(PerformanceRecord.record_date >= start_date)
        if end_date:
            query = query.filter(PerformanceRecord.record_date <= end_date)

        return query.order_by(PerformanceRecord.record_date.desc()).all()

    def get_monthly_summary(
        self, year: int, month: int, user_id: Optional[int] = None
    ) -> list[dict[str, Any]]:
        """Get monthly performance summary aggregated by user.

        Args:
            year: Year
            month: Month (1-12)
            user_id: Optional user ID filter

        Returns:
            List of dictionaries with summary data
        """
        query = (
            self.session.query(
                PerformanceRecord.user_id,
                User.full_name.label("user_name"),
                func.sum(PerformanceRecord.deals_count).label("total_deals"),
                func.sum(PerformanceRecord.showings_count).label("total_showings"),
                func.sum(PerformanceRecord.new_customers_count).label("total_new_customers"),
                func.sum(PerformanceRecord.followup_visits_count).label("total_followups"),
                func.sum(PerformanceRecord.daily_reward).label("total_reward"),
                func.avg(PerformanceRecord.daily_reward).label("avg_daily_reward"),
                func.count(PerformanceRecord.id).label("working_days"),
            )
            .join(User, PerformanceRecord.user_id == User.id)
            .filter(
                and_(
                    extract("year", PerformanceRecord.record_date) == year,
                    extract("month", PerformanceRecord.record_date) == month,
                )
            )
        )

        if user_id:
            query = query.filter(PerformanceRecord.user_id == user_id)

        query = query.group_by(PerformanceRecord.user_id, User.full_name)
        query = query.order_by(func.sum(PerformanceRecord.daily_reward).desc())

        results = query.all()

        return [
            {
                "user_id": r.user_id,
                "user_name": r.user_name,
                "month": f"{year}-{month:02d}",
                "total_deals": r.total_deals or 0,
                "total_showings": r.total_showings or 0,
                "total_new_customers": r.total_new_customers or 0,
                "total_followups": r.total_followups or 0,
                "total_reward": float(r.total_reward or 0),
                "avg_daily_reward": float(r.avg_daily_reward or 0),
                "working_days": r.working_days or 0,
            }
            for r in results
        ]

    def get_top_earners(self, year: int, month: int, limit: int = 10) -> list[dict[str, Any]]:
        """Get top earners for a specific month.

        Args:
            year: Year
            month: Month (1-12)
            limit: Number of top earners to return

        Returns:
            List of top earners with summary data
        """
        results = self.get_monthly_summary(year, month)
        return results[:limit]

    def upsert(
        self,
        user_id: int,
        record_date: date,
        deals_count: int,
        showings_count: int,
        new_customers_count: int,
        followup_visits_count: int,
    ) -> PerformanceRecord:
        """Create or update performance record for a user and date.

        Args:
            user_id: User ID
            record_date: Record date
            deals_count: Number of deals
            showings_count: Number of showings
            new_customers_count: Number of new customers
            followup_visits_count: Number of follow-up visits

        Returns:
            Created or updated PerformanceRecord
        """
        # Calculate reward
        daily_reward = PerformanceRecord.calculate_reward(
            deals_count, showings_count, new_customers_count, followup_visits_count
        )

        # Check if record exists
        existing = self.get_by_user_and_date(user_id, record_date)

        if existing:
            # Update existing record
            existing.deals_count = deals_count
            existing.showings_count = showings_count
            existing.new_customers_count = new_customers_count
            existing.followup_visits_count = followup_visits_count
            existing.daily_reward = daily_reward
            self.session.commit()
            self.session.refresh(existing)
            return existing
        else:
            # Create new record
            record = PerformanceRecord(
                user_id=user_id,
                record_date=record_date,
                deals_count=deals_count,
                showings_count=showings_count,
                new_customers_count=new_customers_count,
                followup_visits_count=followup_visits_count,
                daily_reward=daily_reward,
            )
            self.session.add(record)
            self.session.commit()
            self.session.refresh(record)
            return record
